package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import javax.print.DocFlavor.STRING;

import com.szholly.data.general.*;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.plug.safe.entity.user.UserEntity;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.utils.session.SessionFactory;
import com.szholly.utils.session.provider.ISessionProvider;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.utils.util.StringUtils;
import org.springframework.stereotype.Service;

@Service
public class OnlineMonitorService {
	
	public List<RowBase> getVideoDataByNum(String num) {
		String sql=" select * from BC_VIDEO_INFO where 1=1 ";
		List<GlobalParameter> listParam = new ArrayList<>();
		if(num !=null&&num.equals("")){
			if(num.equals("24")){
				sql+=" and ROWNUM <= 24";
			}
			else if(num.equals("48")){
				sql+=" and ROWNUM > 24 and ROWNUM <= 48";
			}
			else {
				sql+=" and ROWNUM > 48";
			}
		}
		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	/**
	 * 根据污水厂id获取视频信息
	 * @param wwtpId
	 * @return
	 */
	public List<RowBase> getVideoDataByWWTPID(String wwtpId,String station_id) {
		String sql = " select t2.name,t1.* from BC_VIDEO_INFO t1 left join BC_SITE_INFO t2 on t1.station_code=t2.exp_no left join BC_ENTERPRISE_ATTR t3 on t2.wwtp_id=t3.exp_no "
				+ " where 1=1  ";
		List<GlobalParameter> listParam = new ArrayList<>();
       if( wwtpId != null && !wwtpId.equals("")){
    	   sql+=" and t3.exp_no = @wwtp_id";
    	   listParam.add(new GlobalParameter("wwtp_id", wwtpId));
       }
       if(station_id != null && !station_id.equals("")){
    	   sql+=" and t2.exp_no = @station_id";
    	   listParam.add(new GlobalParameter("station_id", station_id));
       }
		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	/**
	 * 根据污水厂id获取DVR信息
	 * @param wwtpId
	 * @return
	 */
	public List<RowBase> getDVRDataByWWTPID(String wwtpId,String station_id) {
		String sql = " select t2.name,t1.* from BC_DVR_INFO t1 left join BC_SITE_INFO t2 on t1.station_code=t2.exp_no left join BC_ENTERPRISE_ATTR t3 on t2.wwtp_id=t3.exp_no "
				+ " where 1=1  ";
		List<GlobalParameter> listParam = new ArrayList<>();
       if( wwtpId != null && !wwtpId.equals("")){
    	   sql+=" and t3.exp_no = @wwtp_id";
    	   listParam.add(new GlobalParameter("wwtp_id", wwtpId));
       }
       if(station_id != null && !station_id.equals("")){
    	   sql+=" and t2.exp_no = @station_id";
    	   listParam.add(new GlobalParameter("station_id", station_id));
       }
		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	
	/**
	 * 根据污水处理厂id获取运营单位信息
	 */
	public List<RowBase> getOperationUnitByWWTPID(String wwtpId) {
		String sql = " select ou.* from BC_ENTERPRISE_ATTR wwtp " + "	left join OPERATIONS_UNIT ou ON ou.guid = wwtp.OPERATOR"
				+ " where wwtp.exp_no = @wwtp_id ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("wwtp_id", wwtpId));

		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}

	/**
	 * 根据站点id获取运营单位信息
	 */
	public List<RowBase> getOperationUnitByStationId(String stationId) {
		String sql = " select ou.* from BC_SITE_INFO t "
				+ "	left join OPERATIONS_UNIT ou ON ou.guid = t.OPERATIONSUNIT_ID" + " where t.exp_no = @station_id ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("station_id", stationId));

		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}

	/**
	 * 获取运营单位
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getOperationUnit(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " select * from  OPERATIONS_UNIT where 1=1";
		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("GUID")) {
			sql += " and GUID=@GUID";
			listParam.add(new GlobalParameter("GUID", params.get("GUID")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}

	/**
	 * 获取数采仪信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getDataCollectionInstrument(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,t2.name as SITE_NAME,t3.name as OPERATORNAME FROM BC_COLLECTION_INFO t "
				+ "	left join BC_SITE_INFO t2 on t.SITE_ID=t2.code "
				+ " left join OPERATIONS_UNIT t3 on t.operation_unit=t3.guid " + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.SITE_ID=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}

	/**
	 * 获取在线监测设备信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getMonitorEquipment(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,ms.name as SITE_NAME from BC_SITE_DEVICE_RPT t"
				+ " left join BC_SITE_INFO ms on t.MONITOR_POINT=ms.CODE" + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.MONITOR_POINT=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}

	/**
	 * 获取站点污染物
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getStationPollutant(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t1.*,t2.name as STATIONNAME from BC_SITE_POLL t1 "
				+ "	left join BC_SITE_INFO t2 on t1.SITE_ID=t2.code " + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t2.wwtp_id=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("waterType")) {
			sql += " and t2.STATION_CATEGORY=@STATION_CATEGORY";
			listParam.add(new GlobalParameter("STATION_CATEGORY", params.get("waterType")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t1.SITE_ID=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("deviceId")) {
			sql += " and t1.DEVICE_ID=@deviceId";
			listParam.add(new GlobalParameter("deviceId", params.get("deviceId")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 获取站点污染物报警设置（包括报警阈值限、角色、用户）
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<List<RowBase>> getStationPollAlarmThresholdSet(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = "select  t3.name,\n" +
						"	 t3.name station_name,"+
						"  t.name pollcode,"+
						"  t3.code,\n" + 
						"  t1.device_num MN,\n" + 
						"   t5.s_domaincaption poll_name,\n" + 
						"  t2.alarm_thred_num,\n" + 
						"  t2.guid,\n" + 
						"  t5.s_domaincode,\n" + 
						"  t2.density_alarm_uplimit,\n" + 
						"  t2.density_alarm_dowmlimit,\n" + 
						" t6.c_rolename ROLE_NAME,\n" + 
						" t6.c_userealname user_name,\n" + 
						"length(t6.c_rolename)-length(replace(t6.c_rolename,',',''))+1 roleNum"+
						" from BC_SITE_POLL t\n" + 
						"  left join BC_COLLECTION_INFO t1 on t1.site_id = t.site_id\n" + 
						"  left join BC_POLL_THRESHOLD t2 on t1.device_num = t2.mn and t2.pollute_code=t.name\n" + 
						"  left join BC_SITE_INFO t3 on t3.exp_no = t.site_id\n" + 
						"  left join view_poll_alarm_role_user t6 on t6.alarm_threhold_guid = t2.guid\n" + 
						"  left join data_defulatdomain t5 on t5.s_domaincode = t.name and t5.s_domainname = 'MONITOR_PARAM'\n" + 
						"  where 1=1";
		
		List<GlobalParameter> listParam = new ArrayList<>();
		if(params.containsKey("station_id")) {
			sql+=" and t.site_id=@site_id \n";
			listParam.add(new GlobalParameter("site_id", params.get("station_id")));
		}
		if (params.containsKey("pollCode")) {
			sql+= " and t.name=@pollCode \n";
			listParam.add(new GlobalParameter("pollCode", params.get("pollCode")));
		}
		sql+=" order by t3.name,t.name,t2.alarm_thred_num";
		List<RowBase> result = DataUtil.getSelectRows(sql, listParam);
		
		RowBase prevRow = null;
		HashMap<String, Integer> stationSpanMap = new HashMap<>();
		HashMap<String, Integer> pollSpanMap = new HashMap<>();
		for(int i=0;i<result.size();i++){
			RowBase row = result.get(i);
			String stationName = row.getSafeString("STATION_NAME");
			String pollcode = row.getSafeString("POLLCODE");
			
			
			if(prevRow == null){
				prevRow = row;
			}
			
			String prevStationName = prevRow.getSafeString("NAME");
			String prevPollcode = prevRow.getSafeString("POLLCODE");
			
			if(!stationSpanMap.containsKey("STATION_NAME_ROWSPAN")){
				stationSpanMap.put("STATION_NAME_ROWSPAN",1);
			}
			
			if(!pollSpanMap.containsKey("POLLCODE_ROWSPAN")){
				pollSpanMap.put("POLLCODE_ROWSPAN",1);
			}
			if(i>0){
				if(stationName.equals(prevStationName)){
					int curStationSpan = stationSpanMap.get("STATION_NAME_ROWSPAN");
					curStationSpan++;
					row.setValue("STATION_NAME_SAME_WITH_PREV", "true");
					stationSpanMap.put("STATION_NAME_ROWSPAN",curStationSpan);
					if(pollcode.equals(prevPollcode)){
						int pollCodeSpan = pollSpanMap.get("POLLCODE_ROWSPAN");
						pollCodeSpan++;
						row.setValue("POLLCODE_SAME_WITH_PREV", "true");
						pollSpanMap.put("POLLCODE_ROWSPAN",pollCodeSpan);
					}
					else{
						pollSpanMap = new HashMap<>();
					}
				}
				else{
					stationSpanMap = new HashMap<>();
				}
			}
			
			row.setValue("POLLCODE_ROWSPAN", pollSpanMap);
			row.setValue("STATION_NAME_ROWSPAN", stationSpanMap);
		}
		
		List<List<RowBase>> Result = new ArrayList<List<RowBase>>();
		Result.add(result);
		return Result;
	}
	
	/**
	 * 查询单条污染物的详情，包括污染物阈值集合
	 * @param params
	 * @return
	 */
	public List<RowBase> getPollAlarmDetail(Map<String, String> params){
		String sql ="SELECT t.abnormal_min,\n" +
						"       t.abnormal_max,\n" + 
						"       t.abnormal_alarm_down,\n" + 
						"       t.abnormal_alarm_up,\n" + 
						"       t.name,\n" + 
						"		t4.s_domaincaption,\n"+
						"		t1.device_num,"	+
						"       t2.density_alarm_dowmlimit,\n" + 
						"       t2.density_alarm_uplimit,\n" + 
						"       t2.guid alarmGuid,\n" + 
						"       t2.ALARM_THRED_NUM,\n" + 
						"       T1.DEVICE_NUM\n" + 
						"  from BC_SITE_POLL t\n" + 
						"  left join BC_COLLECTION_INFO t1 on t1.site_id = t.site_id\n" + 
						"  left join BC_POLL_THRESHOLD t2 on t2.mn = t1.device_num and t.name = t2.pollute_code\n" + 
						"  left join BC_POLL_ROLE t3 on t2.guid = t3.alarm_threhold_guid\n" + 
						"  left join data_defulatdomain t4 on t4.s_domaincode=t.name"+
						" where 1=1\n"
						+" and t4.s_domainname='MONITOR_PARAM' ";
						
		List<GlobalParameter> parms = new ArrayList<>();
		String stationId = params.get("station_id");
		String pollCode = params.get("pollCode");
		if(!StringUtil.IsNullOrEmpty(stationId)) {
			parms.add(new GlobalParameter("stationId",stationId));
			sql+=" and t.site_id = @stationId\n";
		}
		if(!StringUtil.IsNullOrEmpty(pollCode)) {
			parms.add(new GlobalParameter("pollCode",pollCode));
			sql+=" and t.name = @pollCode\n";
		}
	   sql+=" group by t.abnormal_min,\n" +
			"         t.abnormal_max,\n" + 
			"         t.abnormal_alarm_down,\n" + 
			"         t.abnormal_alarm_up,\n" + 
			"         t.name,\n" + 
			"         t4.s_domaincaption,\n"+
			"         t1.device_num,\n"+
			"         t2.density_alarm_dowmlimit,\n" + 
			"         t2.density_alarm_uplimit,\n" + 
			"         t2.guid,\n" + 
			"         t2.ALARM_THRED_NUM,\n" + 
			"         T1.DEVICE_NUM\n" +
			" order by t.name, t2.density_alarm_dowmlimit";
		List<RowBase> result = DataUtil.getSelectRows(sql, parms);
		return result;
	}
	
	public List<RowBase> getPollAlarmRoles(ITable pTable, PageInfo pageInfo)
			throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		String sql = "select tt.*,t4.role_code, t4.user_id from "
				+ " (select t1.*,t3.abnormal_min,t3.abnormal_max from BC_POLL_THRESHOLD t1" 
		        +" left join BC_COLLECTION_INFO t2 on t1.mn=t2.device_num"
		        +" left join  BC_SITE_POLL t3 on t2.site_id=t3.site_id ) tt"
		        +" left join BC_POLL_ROLE t4 on tt.guid = t4.alarm_threhold_guid"
		        + " WHERE 1=1 ";
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 获取站点参数，给报表的左边栏参数选择服务，值查出name和code
	 * gislin
	 * 2016年4月11日
	 * @return
	 */
	public List<RowBase> getStationPollDict(String stationId){
		
		String[] stationIds = stationId.split(",");
		String inWhereStr = lrq.common.StringUtil.getArrayStrForSql(stationIds, "stationId");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationIds, "stationId");
		
		String sql = "select S_DOMAINCODE code,S_DOMAINCAPTION name,S_UNIT UNIT from BC_SITE_POLL sp\n" +
						"   left join DATA_DEFULATDOMAIN dd on dd.S_DOMAINCODE = sp.name and dd.s_domainname='MONITOR_PARAM'\n" + 
						"  where site_id in "+inWhereStr+" group by S_DOMAINCODE,S_DOMAINCAPTION ,S_UNIT,i_order"+ 
						" order by i_order asc ";

		List<RowBase> result = DataUtil.getSelectRows(sql, pc);
		HashMap<String, String> UnitMap=new HashMap<>();
		UnitMap.put("011", "kg");
		UnitMap.put("060", "kg");
		UnitMap.put("065", "kg");
		UnitMap.put("015", "kg");
		UnitMap.put("101", "kg");
		UnitMap.put("ss", "kg");
		UnitMap.put("cond", "kg");
		UnitMap.put("do", "kg");
		UnitMap.put("029", "kg");
		UnitMap.put("028", "kg");
		UnitMap.put("001", "无量纲");
		for(RowBase row : result){
			String pollCode = row.getSafeString("CODE");
			String unitString = UnitMap.get(pollCode);
			if(unitString == null){
				row.setValue("COU_UNIT", "kg");
			}
			else{
				row.setValue("COU_UNIT", unitString);
			}
			if(row.getSafeString("UNIT").equals(null)||"".equals(row.getSafeString("UNIT"))){
				row.setValue("UNIT", unitString);
				
			}
		}		
		return result;
	}

	/**
	 * 获取污水处理厂流量
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getWWTPFlow(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select * from monitor_data_flow t left join (select i.NAME,i.exp_no from BC_ENTERPRISE_ATTR i) k on t.wwtp_id = k.exp_no where 1 = 1";
		if (params.containsKey("wwtp_id")) {
			sql += " and t.wwtp_id = @wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("start")) {
			sql += " and t.DATA_TIME >= to_date('" + params.get("start") + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		if (params.containsKey("end")) {
			sql += " and t.DATA_TIME <= to_date('" + params.get("end") + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		sql += " order by t.DATA_TIME";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取污水处理厂污水处理费
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getWWTPProcessPrice(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select * from BC_ENTERPRISE_ATTR t where 1=1";
		if (params.containsKey("wwtp_id")) {
			sql += " and t.exp_no = @wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		sql += " order by t.exp_no";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取设备生命周期数据
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getDeviceLifeCycleInfo(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select k.*,case when k.is_oos='1' then months_between(k.oos_date,k.oenging_date) else months_between(sysdate,k.oenging_date) end as months "
				+ " from (select p.guid,ms.code as STATION_CODE,ms.name as STATION_NAME,ms.flow_type,"
				+ "t.device_num,t.device_name,t.device_type,t.oenging_date,case when p.is_oos='1' then '1' else '0' end as is_oos,p.oos_reason,p.oos_date,p.p.img,t.num from ("
				+ " select dci.site_id as STATION_CODE,'0' as device_type,dci.ID as DEVICE_NAME,dci.DEVICE_NUM,dci.OENGING_DATE,null as num from BC_COLLECTION_INFO dci where 1=1 ";

		if (params.containsKey("wwtp_id")) {
			sql += " and dci.wsclc_id=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and dci.site_id=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		sql += " union all ";
		sql += " select me.monitor_point as STATION_CODE,'1' as device_type,me.name as DEVICE_NAME,me.device_num,me.oenging_date,null as num from BC_SITE_DEVICE_RPT me where 1=1";
		if (params.containsKey("wwtp_id")) {
			sql += " and me.wsclc_id=@wwtp_id";
		}
		if (params.containsKey("station_id")) {
			sql += " and me.monitor_point=@station_id";
		}
		
		sql += " union all ";
		sql+=" select ar.station_code  ,'2' as device_type,ar.device_name ,ar.device_num as DEVICE_NUM,ar.oenging_date,ar.num from BC_ASSETS_REGISTER ar where 1=1 ";
		if (params.containsKey("wwtp_id")) {
			sql += " and ar.wwtp_id=@wwtp_id";
		}
		if (params.containsKey("station_id")) {
			sql += " and ar.station_code=@station_id";
		}
		sql += ") t left join BC_DEVICE_LIFECYCLE p on p.station_code=t.station_code and p.device_num=t.device_num"
				+ " left join BC_SITE_INFO ms on t.station_code = ms.code) k where 1=1 ";

		if (params.containsKey("flow_type")) {
			sql += " and k.flow_type=@flow_type";
			listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
		}
      if(params.containsKey("is_oos")){
    	  sql += " and k.is_oos=@is_oos";
    	  listParam.add(new GlobalParameter("is_oos", params.get("is_oos")));
      }
		sql += " order by k.device_type,k.oenging_date";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取站点设备，附带是否验收信息
	 * 
	 * @throws Exception
	 */
	public List<RowBase> getStationDeviceWithAcceptanceInfo(Map<String, String> params, PageInfo pageInfo)
			throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select t.*,ms.name as STATION_NAME,decode(p.status,null,'未验收','1','通过','2','未通过') as status from ("
				+ " select dci.wsclc_id,dci.site_id as STATION_CODE,'0' as device_type,dci.ID as DEVICE_NAME,dci.DEVICE_NUM,dci.equipment_type as SBXH from BC_COLLECTION_INFO dci where 1=1 ";

		if (params.containsKey("wwtp_id")) {
			sql += " and dci.wsclc_id=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and dci.site_id=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		sql += " union all ";
		sql += " select me.wsclc_id,me.monitor_point as STATION_CODE,'1' as device_type,me.name as DEVICE_NAME,me.device_num,me.DEVICE_TYPENUM as SBXH from BC_SITE_DEVICE_RPT me where 1=1";
		if (params.containsKey("wwtp_id")) {
			sql += " and me.wsclc_id=@wwtp_id";
		}
		if (params.containsKey("station_id")) {
			sql += " and me.monitor_point=@station_id";
		}
		sql += ") t left join (select * from (select status,station_code  from BC_DEVICE_ACCE_INFO d where 1=1 ";

		if (params.containsKey("wwtp_id")) {
			sql += " and d.wsclc_id=@wwtp_id";
		}
		if (params.containsKey("station_id")) {
			sql += " and d.station_code=@station_id";
		}
		sql += " order by d.accep_date desc) where rownum = 1) p on p.station_code=t.station_code"
				+ " left join BC_SITE_INFO ms on t.station_code = ms.code";

		sql += " order by t.device_type";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取验收详情信息
	 * 
	 * @throws Exception
	 */
	public List<RowBase> getAcceptanceInfo(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select * from BC_DEVICE_ACCE_INFO d where 1=1 ";
		if (params.containsKey("wwtp_id")) {
			sql += " and d.wsclc_id=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and d.station_code=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		sql += " order by d.create_time desc";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取数采仪掉线记录
	 * 
	 * @param resqMap
	 * @return
	 */
	public List<RowBase> getMNOfflineRcdList(Map<String, String> resqMap) {
		String strMN = resqMap.get("MN");
		String strSTART_TIME = resqMap.get("START_TIME");
		String strEND_TIME = resqMap.get("END_TIME");

		if (strMN.length() < 1)
			return null;

		String strSQL = " select t1.* " + " from MONITOR_ABNORMAL t1 " + " where t1.MN = @MN"
				+ " AND t1.OFFLINE_TIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
				+ " AND t1.OFFLINE_TIME <= to_date('" + strEND_TIME + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("MN", strMN));
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, listParam);
		if (rcdList.size() < 1)
			return null;
		return rcdList;
	}

	/**
	 * 获取站点 数采仪 的唯一编号
	 * 
	 * @param wwtp_id
	 * @param flow_type
	 * @param station_id
	 * @return
	 */
	public String getDataCollectionInstrumentNo(String wwtp_id, String flow_type, String station_id) {
		String sql = "select dc.DEVICE_NUM from BC_COLLECTION_INFO dc"
				+ "  inner join BC_SITE_INFO ms on ms.exp_no = dc.SITE_ID" + "  where 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (StringUtil.IsNotNullOrEmpty(wwtp_id)) {
			sql += " and ms.WWTP_ID = @wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", wwtp_id));
		}
		if (StringUtil.IsNotNullOrEmpty(flow_type)) {
			sql += " and ms.flow_type = @flow_type";
			listParam.add(new GlobalParameter("flow_type", flow_type));
		}
		if (StringUtil.IsNotNullOrEmpty(station_id)) {
			sql += " and ms.code = @station_id";
			listParam.add(new GlobalParameter("station_id", station_id));
		}
		sql += " and rownum = 1";

		if (listParam.size() > 0) {
			List<RowBase> equipments = DataUtil.getSelectRows(sql, listParam);
			if (equipments.size() == 0) {
				return null;
			}

			String monitorEquipmentNo = equipments.get(0).getSafeString("DEVICE_NUM");
			return monitorEquipmentNo;
		}
		return null;
	}

	/**
	 * 获取站点的数采仪信息
	 * 
	 * @param strStationID
	 *            站点编号
	 * @return 数采仪信息 {MN: 数采仪编号}
	 */
	public Map<String, String> getStationDataCollectMN(String strStationID) {
		if (StringUtil.IsNullOrEmpty(strStationID))
			return null;

		String strSQL = " SELECT DEVICE_NUM FROM BC_COLLECTION_INFO WHERE SITE_ID = @SITE_ID	 and rownum=1";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("SITE_ID", strStationID));

		Object objStation = DataUtil.getScale(strSQL, listParam);

		String strMN = (objStation == null ? null : objStation.toString());

		Map<String, String> dcMN = new HashMap<String, String>();
		dcMN.put("MN", strMN);
		return dcMN;
	}

	/**
	 * 获取站点在线监测设备列表
	 * 
	 * @param params
	 * @return
	 */
	public List<Map<String, String>> getStationDeviceList(Map<String, String> params) {

		String strSTATION_ID = params.get("STATION_ID");
		if (strSTATION_ID == null || strSTATION_ID.length() < 1)
			return null;

		String strSQL = " select *  from BC_SITE_DEVICE_RPT t where t.MONITOR_POINT = @STATION_ID ";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("STATION_ID", strSTATION_ID));

		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, listParam, pTable);
		if (rcdList.size() < 1)
			return null;

		List<Map<String, String>> deviceList = new ArrayList<Map<String, String>>();
		for (int i = 0; i < rcdList.size(); i++) {
			Map<String, String> device = new HashMap<String, String>();
			device.put("DEVICE_NUM", rcdList.get(i).getSafeString("DEVICE_NUM"));
			device.put("DEVICE_NAME", rcdList.get(i).getSafeString("NAME"));
			device.put("STATION_ID", rcdList.get(i).getSafeString("MONITOR_POINT"));
			deviceList.add(device);
		}
		if (deviceList.size() < 1)
			return null;

		return deviceList;
	}

	/**
	 * 获取小时数据在一段时间内的数据个数（包含氨氮，总磷，COD之和）
	 * 
	 * @param params
	 *            参数字典，包含:MN-设备唯一编号， START_TIME-开始时间 END_TIME-结束时间
	 * @return
	 */
	public long getRealDataNum(Map<String, String> params) throws Exception {
		if (params == null)
			return 0;

		String strMN = params.get("MN");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");

		String strSQL = " select count(GUID) as count from MONITOR_DATA_HOUR where MN = @MN "
				+ " AND DATATIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
				+ " AND DATATIME <= to_date('" + strEND_TIME + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') "
				+ " AND ( PARMNAME = 'B01' OR PARMNAME = '011' OR PARMNAME = '060' ) ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("MN", strMN));

		Object objStation = DataUtil.getScale(strSQL, listParam);
		return Long.parseLong(objStation.toString());
	}

	/**
	 * 获取天数据在一段时间内的数据个数（包含氨氮，总磷，COD之和）
	 * 
	 * @param params
	 *            参数字典，包含:MN-设备唯一编号， START_TIME-开始时间 END_TIME-结束时间
	 * @return
	 */
	public long getRealDataNum_Day(Map<String, String> params) throws Exception {
		if (params == null)
			return 0;

		String strMN = params.get("MN");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");

		String strSQL = " select count(GUID) as count from monitor_data_day where MN = @MN "
				+ " AND DATATIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
				+ " AND DATATIME <= to_date('" + strEND_TIME + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') "
				+ " AND ( PARMNAME = 'B01' OR PARMNAME = '011' OR PARMNAME = '060' ) ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("MN", strMN));

		Object objStation = DataUtil.getScale(strSQL, listParam);
		return Long.parseLong(objStation.toString());
	}

	/**
	 * 获取小时数据中的有效数据组数, 有效数据为：ERROR_DATA 和 OP_FLAG都没有标识为1 的记录
	 * 
	 * @param params
	 * @return
	 */
	public long getRealValidatePacketNum(Map<String, String> params) {

		if (params == null)
			return 0;

		String strMN = params.get("MN");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");

		String strSQL = " \n" + " select count(QN) as count \n"
				+ " from ( select QN, count(MN) as ITEM_NUM from MONITOR_DATA_HOUR where MN = @MN  \n"
				+ " AND DATATIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND DATATIME <= to_date('" + strEND_TIME + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND ( ERROR_DATA = '0'  OR ERROR_DATA is null) " + " AND ( OP_FLAG = '0'  OR OP_FLAG is null) "
				+ " AND ( PARMNAME = 'B01' OR PARMNAME = '011' OR PARMNAME = '060' ) \n" + " group by QN, MN \n"
				+ " order by QN ) QN \n" + " where QN.ITEM_NUM = 3 \n"; // 默认每组数据中包含3个数据项目(废水、
																		// COD、氨氮)
																		// B01
																		// 001
																		// 011
																		// 060
																		// 101
																		// B01TOTAL
		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("MN", strMN));

		Object objStation = DataUtil.getScale(strSQL, listParam);
		return Long.parseLong(objStation.toString());
	}

	/**
	 * 查询站点
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryStation(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		String sql = "select t.*,dc.status onoffstatus,dc.device_num  from BC_SITE_INFO t\n"
				+ "       left join BC_COLLECTION_INFO dc on t.code = dc.site_id \n" 
				+ "       where 1=1 ";

		TwoTuple<String, List<GlobalParameter>> p = getStationQueryParams(params);
		String stationStatus = params.get("stationStatus");
		sql += p.first;
		String catergeory=params.get("water_type");
		if(catergeory.equals("3")){
		sql += " order by to_number(dc.site_id) asc nulls last,dc.status desc nulls last";}
		else{
			sql += " order by dc.status desc nulls last,dc.site_id desc nulls last";
		}
		List<RowBase> rows;
		if (pageInfo == null) {
			rows = DataUtil.getSelectRows(sql, p.second, pTable);
		} else {
			rows = DataUtil.getPageRow(sql, p.second, pTable, pageInfo);
		}
		
		List<RowBase> resultRows = new ArrayList<>();
		for(RowBase row : rows){
			String mn = row.getSafeString("DEVICE_NUM");
			String status = RedisHelper.getInstance().getStationStatus(mn);
			if(status == null || status.trim().equals("")){
				status = "3";
			}
			row.setValue("STATION_STATUS",status);
			if(stationStatus != null && stationStatus.equals(status)){
				resultRows.add(row);
			}
			else if(stationStatus == null){
				resultRows.add(row);
			}
		}
		return resultRows;
	}

	/**
	 * 查询站点坐标数据
	 */
	public List<RowBase> queryStationWithSpatial(Map<String, String> params, ITable pTable, PageInfo pageInfo) {
		String sql = "select pw.name as wwtp_name,area.s_domaincaption area_name,city.s_domaincaption city_name,'0' AS ONOFFSTATUS,dc.device_num,t.*,t.code as objectid,'POINT('||t.x||' '||t.y||')' as wkt from BC_SITE_INFO t"
				+" left join BC_ENTERPRISE_ATTR pw on t.wwtp_id=pw.exp_no "
				+ " left join BC_COLLECTION_INFO dc on t.code=dc.site_id"
				+ " left join data_defulatdomain city on city.s_domainname = '行政区划' and city.s_domaincode = t.city_code"
				+"	left join data_defulatdomain area on area.s_domainname = '行政区划' and area.s_domaincode = t.area_code where 1=1 ";
		
		ISessionProvider session = SessionFactory.getSession();
		UserEntity userEntity = (UserEntity) session.getObject(ISessionProvider.UserEntity);
		String userId = userEntity.getUserID();
		TwoTuple<String, List<GlobalParameter>> p = getStationQueryParams(params);
		
		if(!userId.equals("-1")){
			sql += " AND t.code in ( select station_code from OC_ROLE_SITE_PRIVILEGE t3"+
		               "     inner join safe_userrole t4 on t4.s_userid = @userid and t3.role_id = t4.s_roleid) ";
			p.second.add(new GlobalParameter("userid", userId));
		}
		

		sql += p.first; 
		sql+=" order by dc.status desc nulls last,dc.site_id desc nulls last";
		String stationStatus = params.get("stationStatus");
		List<RowBase> rows = DataUtil.getSelectRows(sql, p.second, pTable);
		List<RowBase> resultRows = new ArrayList<>();
		for(RowBase row : rows){
			String mn = row.getSafeString("DEVICE_NUM");
			String status = RedisHelper.getInstance().getStationStatus(mn);
			if(status == null || status.trim().equals("")){
				status = "3";
			}
			row.setValue("STATION_STATUS",status);
			if(stationStatus != null && stationStatus.equals(status)){
				resultRows.add(row);
			}
			else if(stationStatus == null){
				resultRows.add(row);
			}
		}
		return resultRows;
	}
	
	/**
	 * 获取设备测量的参数
	 * gislin
	 * 2016年3月31日
	 * @param deviceId
	 * @return
	 */
public List<RowBase> getPollutantsByDeviceId(String deviceId){
		
		String sql = " SELECT t.*,t1.s_domaincaption NAME_DESC,t2.name as STATIONNAME  from BC_SITE_POLL t"
				+ " left join BC_SITE_INFO t2 on t2.code=t.site_id"
				+ " left join data_defulatdomain t1 on t1.s_domaincode = t.name and t1.s_domainname = 'MONITOR_PARAM' "
				+ " WHERE device_id = @deviceId ";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("deviceId",deviceId));
		
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);		
		return rows;
	}


	private TwoTuple<String, List<GlobalParameter>> getStationQueryParams(Map<String, String> params) {
		StringBuilder sql = new StringBuilder();
		List<GlobalParameter> listParam = new ArrayList<>();

		if (params.containsKey("wwtp_id")) {
			sql.append(" and t.WWTP_ID=@wwtp_id");
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("flow_type")) {
			sql.append(" and t.flow_type=@flow_type");
			listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
		}
		if (params.containsKey("station_id")) {
			sql.append(" and t.code=@station_id");
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("station_id_like")) {
			sql.append(" and t.code like '__" + params.get("station_id_like") + "%'");
		}
		if (params.containsKey("station_name")) {
			sql.append(" and t.name like '%" + params.get("station_name") + "%'");
		}
		if (params.containsKey("water_type")) {
			sql.append(" and t.station_category=@water_type");
			listParam.add(new GlobalParameter("water_type", params.get("water_type")));
		}
		if (params.containsKey("build_status")) {
			sql.append(" and t.build_status=@build_status");
			listParam.add(new GlobalParameter("build_status", params.get("build_status")));
		}
		if (params.containsKey("companyCode")) {
			sql.append(" and t.wwtp_id=@companyCode");
			listParam.add(new GlobalParameter("companyCode", params.get("companyCode")));
		}
		if (params.containsKey("s_domaincode_like")) {
			String type=params.get("water_type");
			if(type.equals("0")){
				sql.append(" and t.area_code like '__" + params.get("s_domaincode_like") + "%'");
			}
			else{
				sql.append(" and t.area_code like '______" + params.get("s_domaincode_like") + "%'");
			}
			
			listParam.add(new GlobalParameter("s_domaincode_like", params.get("s_domaincode_like")));
		}
	
		
		return new TwoTuple<String, List<GlobalParameter>>(sql.toString(), listParam);
	}
	/**
	 * 根据GUID查询之前设置的阈值，以及所有的角色和用户
	 * @param GUID
	 * @param Mn
	 * @param pollCode
	 * @return
	 */
	public List<List<RowBase>> queryOldRoleAndUser(String GUID,String Mn,String pollCode) {
		List<List<RowBase>> result = new ArrayList<List<RowBase>>();
		List<GlobalParameter> parms = new ArrayList<>();
		//根据GUID查询该阈值对应的角色和用户
		String qryOldRoleUserSql="select tt.*,t3.c_rolename,t4.c_userealname from\n"
								+" (select distinct t1.guid , t2.role_code,t2.user_id from BC_POLL_THRESHOLD t1\n"
								+ " left join BC_POLL_ROLE t2 on  t1.guid=t2.ALARM_THREHOLD_GUID )tt\n" 
								+"left join safe_role t3 on tt.role_code=t3.i_roleid \n"
								+"left join safe_user t4 on tt.user_id=t4.s_userid\n"
								+"where 1=1\n ";
		if(!StringUtils.IsNullOrEmpty(GUID)) {
			qryOldRoleUserSql +=" and guid=@GUID";
			parms.add(new GlobalParameter("GUID",GUID));
		}
		List<RowBase> OldRoleUserRlt = DataUtil.getSelectRows(qryOldRoleUserSql, parms);
		//查询所有的角色
		String qryAllRoleSql = "select * from safe_role ";
		List<RowBase> allRoleList = DataUtil.getSelectRows(qryAllRoleSql, null);
		//查询所有的用户
		String qryAllUserSql = "select * from safe_user ";
		List<RowBase> allUserList = DataUtil.getSelectRows(qryAllUserSql, null);
		result.add(OldRoleUserRlt);
		result.add(allRoleList);
		result.add(allUserList);
		return result;
	}
	/**
	 * 查询所有的角色和用户
	 * @return
	 */

	public List<List<RowBase>> getAlarmRoleAndUserSet(String GUID) {
		List<List<RowBase>> result = new ArrayList<List<RowBase>>();
		List<GlobalParameter> parms = new ArrayList<>();
		if(GUID!=null && !"".equals(GUID)) {
				String oldRoleSql="select distinct t5.i_roleid roleid, t5.c_rolename role_name\n" +
								"        from BC_POLL_THRESHOLD t1\n" + 
								"        left join BC_POLL_ROLE t2 on t1.guid = t2.alarm_threhold_guid\n" + 
								"        left join safe_user t3 on t2.user_id = t3.s_userid\n" + 
								"        left join safe_userrole t4 on t4.s_userid = t3.s_userid\n" + 
								"        left join safe_role t5 on t5.i_roleid = t4.s_roleid\n"
								+" 		  where 1=1 ";	
				if(!StringUtils.IsNullOrEmpty(GUID)) {
					oldRoleSql +=" and t1.guid=@GUID";
					parms.add(new GlobalParameter("GUID",GUID));
				}
				List<RowBase> RoleRlt = DataUtil.getSelectRows(oldRoleSql, parms);
				if(RoleRlt != null) {
					result.add(RoleRlt);
				} else {
					result.add(null);
				}
				parms.clear();
			String oldUserSql = "select t3.c_userealname user_name, t3.s_userid userid\n" +
									"  from BC_POLL_THRESHOLD t1\n" + 
									"  left join BC_POLL_ROLE t2 on t1.guid = t2.alarm_threhold_guid\n" + 
									"  left join safe_user t3 on t2.user_id = t3.s_userid"+
									" where 1 = 1";
			if(!StringUtils.IsNullOrEmpty(GUID)) {
				oldUserSql +=" and t1.guid=@GUID";
				parms.add(new GlobalParameter("GUID",GUID));
			}
			List<RowBase> UserRlt = DataUtil.getSelectRows(oldUserSql, parms);
			if(UserRlt!=null) {
				result.add(UserRlt);
			} else {
				result.add(null);
			}
		}else {
			result.add(null);
			result.add(null);
		}
		//查询所有的角色
		String qryAllRoleSql = "select tt.c_rolename ROLE_NAME,tt.i_roleid roleid from safe_role tt";
		List<RowBase> allRoleList = DataUtil.getSelectRows(qryAllRoleSql, null);
		//查询所有的用户
		String qryAllUserSql = "select t.c_userealname USER_NAME,t.s_userid userid from safe_user t";
		//用户去重
		List<RowBase> allUserList = DataUtil.getSelectRows(qryAllUserSql, null);
		allUserList = delteTheSameUser(allUserList);
		result.add(allRoleList);
		result.add(allUserList);
		return result;
	}
	/**
	 * 查询角色对应的用户
	 */
	public List<RowBase> queryUserOfRole(String roleId) {
		String[] roleArray = roleId.split(",");
		String roleStr="";
		for(int k=0;k<roleArray.length;k++) {
			roleStr+="'"+roleArray[k]+"',";
		}
		roleStr=roleStr.substring(0, roleStr.length()-1);
		List<RowBase> result = new ArrayList<RowBase>();
		List<GlobalParameter> parms = new ArrayList<>();
		//查询所有的用户
		String qryUserOfRoleSql = " select distinct t.s_userid userid,t2.c_userealname USER_NAME from safe_userrole t \n"
								+" left join safe_user t2 on t.s_userid=t2.s_userid left join safe_role t3 on t.s_roleid = t3.i_roleid where 1=1 \n";
		if(!StringUtils.IsNullOrEmpty(roleStr)) {
			qryUserOfRoleSql+=" and t.s_roleid in ("+roleStr+")";
			result = DataUtil.getSelectRows(qryUserOfRoleSql, parms);
		} else {
			result= null;
		}
		return result;
	}
	/**
	 * 根据userid查询username
	 */	public List<RowBase> queryUserNamesById(String UserId) {
		String[] userId = UserId.split(",");
		String sql=" select *  from safe_user where 1=1 \n";
		String inSql=" and S_USERID in(";
		if(userId.length>0) {
			for(int i=0;i<userId.length;i++) {
				inSql+="'"+userId[i]+"',";
			}
			inSql=inSql.substring(0, inSql.length()-1)+")";
			sql+=inSql;
		}
		List<RowBase>result = DataUtil.getSelectRows(sql, null,null);
		return result;
	}
	 /**
	  * 根据GUID删除数据
	  * @param GUID
	  */
	public void deletePollAlarmByGUID(String GUID) {
		if(GUID!=null && !"".equals(GUID)) {
			String deltSql=" delete from BC_POLL_THRESHOLD t where t.guid='"+GUID+"'";
			String deltRoleUserSql = " delete from  BC_POLL_ROLE t1 where t1.alarm_threhold_guid='"+GUID+"'";
			DataUtil.ExecuteUpdateSql(deltSql, null);
			DataUtil.ExecuteUpdateSql(deltRoleUserSql, null);
		}
	}
	
	/**
	 * GUID:WERE1WE14WE42EW1ER;DENSITY_ALARM_DOWMLIMIT:6;DENSITY_ALARM_UPLIMIT:7;ALARM_NAME:阈值1;USERS:1,2,3,4,5,6;ROLES:a,b,c,d@
	 * @param dataStr
	 */
	public void saveAndUpdatePollAlarmInfo(HashMap<String, String> viewValues){
		
		String stationId = viewValues.get("site_id");
		String mn = viewValues.get("mn");
		String pollCode = viewValues.get("pollCode");
		String abnormalMin = viewValues.get("ABNORMAL_MIN");
		String abnormalMax = viewValues.get("ABNORMAL_MAX");
		String abnormalAlarmDown = viewValues.get("ABNORMAL_ALARM_DOWN");
		String abnormalAlarmUp = viewValues.get("ABNORMAL_ALARM_UP");
		String alarmSets = viewValues.get("alarmSets");
		String[] alarmItems = alarmSets.split("@");
		List<GlobalParameter> parms = new ArrayList<>();
		
		if(StringUtil.IsNullOrEmpty(stationId) ||
				StringUtil.IsNullOrEmpty(mn)){
			return;
		}
		
		String updateSql = "";
		if(StringUtil.IsNotNullOrEmpty(abnormalMin)){
			updateSql += "t.ABNORMAL_MIN=@abnormalMin,";
		}
		if(StringUtil.IsNotNullOrEmpty(abnormalMax)){
			updateSql += "t.ABNORMAL_MAX=@abnormalMax,";
		}
		if(StringUtil.IsNotNullOrEmpty(abnormalAlarmDown)){
			updateSql += "t.ABNORMAL_ALARM_DOWN=@abnormalAlarmDown,";
		}
		if(StringUtil.IsNotNullOrEmpty(abnormalAlarmUp)){
			updateSql += "t.ABNORMAL_ALARM_UP=@abnormalAlarmUp,";
		}
		
		if(StringUtil.IsNotNullOrEmpty(updateSql)){
			updateSql = lrq.common.StringUtil.replaceLast(updateSql,",","");
			updateSql = " update BC_SITE_POLL t set " + updateSql + " where  t.site_id=@stationId and t.name=@pollCode";
			parms.add(new GlobalParameter("abnormalMin",abnormalMin));
			parms.add(new GlobalParameter("abnormalMax",abnormalMax));
			parms.add(new GlobalParameter("abnormalAlarmDown",abnormalAlarmDown));
			parms.add(new GlobalParameter("abnormalAlarmUp",abnormalAlarmUp));
			parms.add(new GlobalParameter("stationId",stationId));
			parms.add(new GlobalParameter("pollCode",pollCode));
			DataUtil.ExecuteUpdateSql(updateSql, parms);
		}
		
		for(int i=0;i<alarmItems.length;i++){
			
			parms.clear();
			
			String alarmItem = alarmItems[i];
			String guid = getFieldValueForStringData(alarmItem,"GUID");
			String densityAlarmDown = getFieldValueForStringData(alarmItem,"DENSITY_ALARM_DOWMLIMIT");
			String densityAlarmUp = getFieldValueForStringData(alarmItem,"DENSITY_ALARM_UPLIMIT");
			String alarmName = getFieldValueForStringData(alarmItem,"ALARM_NAME");
			String isClearAllUser = getFieldValueForStringData(alarmItem,"isClearAllUser");
			String users = getFieldValueForStringData(alarmItem,"USERS");
			
			if(StringUtil.IsNullOrEmpty(alarmItem) || 
					StringUtil.IsNullOrEmpty(densityAlarmDown) || 
					StringUtil.IsNullOrEmpty(densityAlarmUp)){
				continue;
			}
			
			parms.add(new GlobalParameter("densityAlarmDown", densityAlarmDown));
			parms.add(new GlobalParameter("densityAlarmUp", densityAlarmUp));
			parms.add(new GlobalParameter("mn", mn));
			parms.add(new GlobalParameter("pollCode", pollCode));
			parms.add(new GlobalParameter("alarmName", alarmName));
			
			if(StringUtil.IsNotNullOrEmpty(guid)){
				parms.add(new GlobalParameter("guid", guid));
				String updatePollAlarmSql = " update BC_POLL_THRESHOLD t set t.DENSITY_ALARM_DOWMLIMIT=@densityAlarmDown,t.DENSITY_ALARM_UPLIMIT=@densityAlarmUp,t.alarm_thred_num=@alarmName\n"
						+" where t.guid=@guid";
				DataUtil.ExecuteUpdateSql(updatePollAlarmSql, parms);
			} else{
				guid = UUID.randomUUID().toString();
				parms.add(new GlobalParameter("guid", guid));
				String alarmThredSql=" insert into  BC_POLL_THRESHOLD"
						+ "(guid, mn, pollute_code, density_alarm_dowmlimit, density_alarm_uplimit,alarm_thred_num) values"
						+ "(@guid,@mn,@pollCode,@densityAlarmDown,@densityAlarmUp,@alarmName)";
				DataUtil.ExecuteUpdateSql(alarmThredSql, parms);
			}
			
			if(StringUtil.IsNotNullOrEmpty(users)){
				if(StringUtil.IsNotNullOrEmpty(guid)){
					String deltRoleUserByAlarmGuidSql = " delete from BC_POLL_ROLE t where ALARM_THREHOLD_GUID = @guid";
					DataUtil.ExecuteUpdateSql(deltRoleUserByAlarmGuidSql, parms);
				}
				
				String[] useridArray = users.split(",");
				for(int j=0;j<useridArray.length;j++){
					parms.clear();
					String userid = useridArray[j];
					String insertRoleUserSql=" insert into  BC_POLL_ROLE"
							+ "(guid, mn, pollute_code, user_id,ALARM_THREHOLD_GUID) values"
							+ "(sys_guid(),@mn,@pollCode,@userid,@guid)";
					parms.add(new GlobalParameter("mn", mn));
					parms.add(new GlobalParameter("pollCode", pollCode));
					parms.add(new GlobalParameter("userid", userid));
					parms.add(new GlobalParameter("guid", guid));						
					DataUtil.ExecuteUpdateSql(insertRoleUserSql, parms);
				}
			} else {
				//针对用户被清空的情况、isClearAllUser用来标识该用户是被清空了的，不是没查出来的
				if("true".equals(isClearAllUser)) {
					if(StringUtil.IsNotNullOrEmpty(guid)){
						String deltRoleUserByAlarmGuidSql = " delete from BC_POLL_ROLE t where ALARM_THREHOLD_GUID = @guid";
						DataUtil.ExecuteUpdateSql(deltRoleUserByAlarmGuidSql, parms);
					}
				}
			}
		}
		
	}
	
	/**
	 * 示例数据：POLL_CODE:011;MN:GL00001;ABNORMAL_MIN:2;ABNORMAL_MAX:3;ABNORMAL_ALARM_DOWN:4;ABNORMAL_ALARM_UP:5;DENSITY_ALARM_DOWMLIMIT:6;DENSITY_ALARM_UPLIMIT:7;USERS:1,2,3,4,5,6;ROLES:a,b,c,d
	 * @return
	 */
	public String getFieldValueForStringData(String data,String field){
		
		String result = null;
		try {
			String[] dataItems = data.split(";");
			for(int i=0;i<dataItems.length;i++){
				String[] itemKeyValue = dataItems[i].split(":");
				if(itemKeyValue.length < 2){
					continue;
				}
				String fieldName = itemKeyValue[0];
				String value = itemKeyValue[1];
				if("null".equals(value)||
						"undefined".equals(value)){
					value = null;
				}
				if(fieldName.equals(field)){
					result = value;
				}
			}
		} catch (Exception e) {
			System.out.println(data);
			System.out.println(field);
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 保存更新站点量程，污染物短信报警阈值
	 */
	public void savePollAarmTest(String site_id,String mn,String pollCodes,String saveData) throws Exception {
		String[] stationPollData = saveData.split(";");
		for(int i=0;i<stationPollData.length;i++) {
			String[] data = stationPollData[i].split(",");
			//第一条数据更新站点污染物表中的异常阈值、量程阈值，更新报警阈值表
			List<GlobalParameter> parms = new ArrayList<>();
			String abnormal_min=data[1];
			String abnormal_max=data[0];
			String abnormal_alarm_down=data[2];
			String abnormal_alarm_up=data[3];
			String stationId=site_id;
			String pollCode=pollCodes;
			
			String guid = data[6];
			
			String MN = mn;
			String poll_alarm_down = data[8];
			String poll_alarm_up = data[9];
			String alarm_thred_num = data[10];
			if(abnormal_min!=null && !"null".equals(abnormal_min)) {
				parms.add(new GlobalParameter("abnormal_min",abnormal_min));
			} else {
				parms.add(new GlobalParameter("abnormal_min",""));
			}
			if(abnormal_max!=null && !"null".equals(abnormal_max)) {
				parms.add(new GlobalParameter("abnormal_max",abnormal_max));
			} else {
				parms.add(new GlobalParameter("abnormal_max",""));
			}
			if(abnormal_alarm_down!=null && !"null".equals(abnormal_alarm_down)) {
				parms.add(new GlobalParameter("abnormal_alarm_down",abnormal_alarm_down));
			} else {
				parms.add(new GlobalParameter("abnormal_alarm_down",""));
			}
			if(abnormal_alarm_up!=null && !"null".equals(abnormal_alarm_up)) {
				parms.add(new GlobalParameter("abnormal_alarm_up",abnormal_alarm_up));
			} else {
				parms.add(new GlobalParameter("abnormal_alarm_up",""));
			}
			if(poll_alarm_down!=null && !"null".equals(poll_alarm_down)) {
				parms.add(new GlobalParameter("poll_alarm_down",poll_alarm_down));
			} else {
				parms.add(new GlobalParameter("poll_alarm_down",""));
			}
			if(poll_alarm_up!=null && !"null".equals(poll_alarm_up)) {
				parms.add(new GlobalParameter("poll_alarm_up",poll_alarm_up));
			} else {
				parms.add(new GlobalParameter("poll_alarm_up",""));
			}
			if(guid!=null && !"null".equals(guid)) {
				parms.add(new GlobalParameter("guid",guid));
			} 
			if(alarm_thred_num!=null && !"null".equals(alarm_thred_num)) {
				parms.add(new GlobalParameter("alarm_thred_num",alarm_thred_num));
			}
			if(guid!=null && !"null".equals(guid)) {
				parms.add(new GlobalParameter("guid",guid));
			}
			//仅第一条数据需要更新量程、异常阈值。
			if(i==0) {
				String updateSql = " update BC_SITE_POLL t set t.ABNORMAL_MIN=@abnormal_min,t.ABNORMAL_MAX=@abnormal_max,t.ABNORMAL_ALARM_DOWN=to_number(@abnormal_alarm_down),\n"
						+" t.ABNORMAL_ALARM_UP=to_number(@abnormal_alarm_up) where 1=1 ";
				
				if(stationId!=null && !"null".equals(stationId)) {
					parms.add(new GlobalParameter("stationId",stationId));
					updateSql+=" and t.site_id=@stationId";
				}
				if(pollCode!=null && !"null".equals(pollCode)) {
					parms.add(new GlobalParameter("pollCode",pollCode));
					updateSql+=" and t.name=@pollCode";
				}
				DataUtil.ExecuteUpdateSql(updateSql, parms);
			} 
			//首先根据GUID来判断是更新还是新增数据到阈值表

			if(guid!=null && !"undefined".equals(guid)&&!"null".equals(guid)) {
				String updatePollAlarmSql = " update BC_POLL_THRESHOLD t set t.DENSITY_ALARM_DOWMLIMIT=@poll_alarm_down,t.DENSITY_ALARM_UPLIMIT=@poll_alarm_up,t.alarm_thred_num=@alarm_thred_num\n"
						+" where t.guid=@guid";
				DataUtil.ExecuteUpdateSql(updatePollAlarmSql, parms);
				//先删除,再根据guid进行新增
				String deltRoleUserByAlarmGuidSql = " delete from   BC_POLL_ROLE t   where ALARM_THREHOLD_GUID = @guid";
				DataUtil.ExecuteUpdateSql(deltRoleUserByAlarmGuidSql, parms);
				for(int k=11;k < data.length; k++) {
					String user=data[k];
					String roleUserGuids = UUID.randomUUID().toString();
					if(!"".equals(user)&&!"undefined".equals(user)) {
						String insertRoleUserSql=" insert into  BC_POLL_ROLE"
								+ "(guid, mn, pollute_code, role_code, user_id,ALARM_THREHOLD_GUID) values"
								+ "('"+roleUserGuids+"',"+"'"+MN+"',"+"'"+pollCode+"',"+"'"+""+"',"+"'"+user+"','"+guid+"')";
						DataUtil.ExecuteUpdateSql(insertRoleUserSql, null);
					}
				}
			} else {
				String guids = UUID.randomUUID().toString();
				String alarmThredSql=" insert into  BC_POLL_THRESHOLD"
						+ "(guid, mn, pollute_code, density_alarm_dowmlimit, density_alarm_uplimit,alarm_thred_num) values"
						+ "('"+guids+"',"+"'"+MN+"',"+"'"+pollCode+"',"+"'"+poll_alarm_down+"',"+"'"+poll_alarm_up+"','"+alarm_thred_num+"')";
				DataUtil.ExecuteUpdateSql(alarmThredSql, null);
				for(int k=11;k < data.length; k++) {
					String user=data[k];
					if(!"".equals(user)) {
						String roleUserGuids = UUID.randomUUID().toString();
						String insertRoleUserSql=" insert into  BC_POLL_ROLE"
								+ "(guid, mn, pollute_code, role_code, user_id,ALARM_THREHOLD_GUID) values"
								+ "('"+roleUserGuids+"',"+"'"+MN+"',"+"'"+pollCode+"',"+"'"+""+"',"+"'"+user+"','"+guids+"')";
						DataUtil.ExecuteUpdateSql(insertRoleUserSql, null);
					}
				}
			}
		}
			
	}
	/**
	 * 对查询的用户进行去重处理
	 * @param dataRow
	 * @return
	 */
	private List<RowBase> delteTheSameUser(List<RowBase> dataRow) {
		List<RowBase> resultRows = new ArrayList<>();
		boolean isAdd=true;
			if(dataRow != null) {
				for(int i=0;i<dataRow.size();i++) {
					RowBase row = dataRow.get(i);
					String name = row.getSafeString("USER_NAME");
					for(int k=i+1;k<dataRow.size();k++) {
						RowBase rows = dataRow.get(k);
						String names = rows.getSafeString("USER_NAME");
						if(name.equals(names)) {
							isAdd=false;
							break;
						}
					}
					if(isAdd) {
						resultRows.add(row);
					}
				}
				return resultRows;
			} else {
				return null;
			}
		}
	
}
